-- gets the "latest" document key given a DocumentKey or a DocumentVersionKey.  If key is DocumentKey
-- that same DocumentKey is returned.  If key is a DocumentVersionKey, returns the DocumentKey of the
-- document with the lowest status (e.g., prefers working over published, published over archived, etc.)
-- and the most recent date
CREATE PROCEDURE [dbo].[asi_DocumentGetLatestKey] @key uniqueidentifier, @documentKey uniqueidentifier OUT AS
BEGIN
   SELECT @documentKey = DocumentKey
     FROM DocumentMain
    WHERE DocumentKey = @key
       OR (
          DocumentVersionKey = @key
      AND CreatedOn = (
          SELECT TOP 1 CreatedOn
            FROM DocumentMain
           WHERE DocumentVersionKey = @key
           ORDER BY DocumentStatusCode, CreatedOn ASC))
END

GO